﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Data;

namespace XSTDZ.ERP.Infrastructure.AdoHelper
{
    public static class SqlHelperEnhance
    {
        private static readonly string connStr =ConfigurationManager.ConnectionStrings["ERPAppContextContainer"].ConnectionString;
        public static string ConnStr
        {
            get
            {
                return connStr;
            }
        }
        public static DataTable ExecuteReaderText(string formatSqlString, params object[] parameterValues)
        {
            string[] parameters = new string[parameterValues.Length];
            SqlParameter[] sqlParameters = new SqlParameter[parameterValues.Length];
            for (int i = 0; i < parameterValues.Length; i++)
            {
                parameters[i] = "@p" + i.ToString();
            }
            for (int i = 0; i < parameterValues.Length; i++)
            {
                sqlParameters[i] = new SqlParameter
                {
                    ParameterName = parameters[i],
                    DbType = SqlMetaData.InferFromValue(parameterValues[i], parameters[i]).DbType,
                    Value = parameterValues[i]
                };
            }
            var dataSet= SqlHelper.ExecuteDataset(connStr, CommandType.Text, string.Format(formatSqlString, parameters), sqlParameters);
            return (dataSet ?? new DataSet()).Tables[0];
        }
        public static int ExecuteScalQueryText(string formatSqlString, params object[] parameterValues)
        {
            string[] parameters;
            SqlParameter[] sqlParameters;
            AutoParameter(parameterValues, out parameters, out sqlParameters);
           var result=  SqlHelper.ExecuteScalar(connStr, CommandType.Text, string.Format(formatSqlString, parameters), sqlParameters);
           return (int)(result ?? 0);
        }

        public static T ExecuteScalQueryText<T>(string formatSqlString, params object[] parameterValues)
        {
            string[] parameters;
            SqlParameter[] sqlParameters;
            AutoParameter(parameterValues, out parameters, out sqlParameters);
            var result = SqlHelper.ExecuteScalar(connStr, CommandType.Text, string.Format(formatSqlString, parameters), sqlParameters);
            return (T)(result ?? 0);
        }

        public static DataTable ExecuteReaderProc(string formatSqlString,  string[] parameterKeys=null, params object[] parameterValues)
        {
            string[] parameters = new string[parameterValues.Length];
            SqlParameter[] sqlParameters = new SqlParameter[parameterValues.Length];
            if (parameterKeys != null && parameterKeys.Length > 0)
            {
                for (int i = 0; i < parameterKeys.Length; i++)
                {
                    parameters[i] = parameterKeys[i].ToString();// "@p" + i.ToString();
                }
            }
            for (int i = 0; i < parameterValues.Length; i++)
            {
                sqlParameters[i] = new SqlParameter
                {
                    ParameterName = parameters[i],
                    DbType = SqlMetaData.InferFromValue(parameterValues[i], parameters[i]).DbType,
                    Value = parameterValues[i]
                };
            }
            var dataSet = SqlHelper.ExecuteDataset(connStr, CommandType.StoredProcedure, string.Format(formatSqlString, parameters), sqlParameters);
            return (dataSet ?? new DataSet()).Tables[0];
        }
        private static void AutoParameter(object[] parameterValues, out string[] parameters, out SqlParameter[] sqlParameters)
        {
            parameters = new string[parameterValues.Length];
            sqlParameters = new SqlParameter[parameterValues.Length];
            for (int i = 0; i < parameterValues.Length; i++)
            {
                parameters[i] = "@p" + i.ToString();
            }
            for (int i = 0; i < parameterValues.Length; i++)
            {
                sqlParameters[i] = new SqlParameter
                {
                    ParameterName = parameters[i],
                    DbType = SqlMetaData.InferFromValue(parameterValues[i], parameters[i]).DbType,
                    Value = parameterValues[i] ?? DBNull.Value
                };
            }
        }
    }
}
